﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class RateDetailDA : IRateDetailDA
    {
        private string BILLING_ENTITY = "@BILLING_ENTITY";
        private string BILLING_OBJECT = "@BILLING_OBJECT";
        private string CARRY_MODE = "@CARRY_MODE";
        private string CHARGE_INVOCATION = "@CHARGE_INVOCATION";
        private string CHARGE_MODE = "@CHARGE_MODE";
        private string CHARGE_PER_UNIT = "@CHARGE_PER_UNIT";
        private string CHARGE_UNIT = "@CHARGE_UNIT";
        private string COST_INVOCATION = "@COST_INVOCATION";
        private string COST_PER_UNIT = "@COST_PER_UNIT";
        private string COST_RATE = "@COST_RATE";
        private string COSTING_UNIT = "@COSTING_UNIT";
        private string CREATED_BY = "@CREATED_BY";
        private string CS_DISCOUNT = "@CS_DISCOUNT";
        private string CUC_FROM01 = "@CUC_FROM01";
        private string CUC_FROM02 = "@CUC_FROM02";
        private string CUC_FROM03 = "@CUC_FROM03";
        private string CUC_FROM04 = "@CUC_FROM04";
        private string CUC_FROM05 = "@CUC_FROM05";
        private string CUC_FROM06 = "@CUC_FROM06";
        private string CUC_FROM07 = "@CUC_FROM07";
        private string CUC_FROM08 = "@CUC_FROM08";
        private string CUC_FROM09 = "@CUC_FROM09";
        private string CUC_FROM10 = "@CUC_FROM10";
        private string CUC_RATE01 = "@CUC_RATE01";
        private string CUC_RATE02 = "@CUC_RATE02";
        private string CUC_RATE03 = "@CUC_RATE03";
        private string CUC_RATE04 = "@CUC_RATE04";
        private string CUC_RATE05 = "@CUC_RATE05";
        private string CUC_RATE06 = "@CUC_RATE06";
        private string CUC_RATE07 = "@CUC_RATE07";
        private string CUC_RATE08 = "@CUC_RATE08";
        private string CUC_RATE09 = "@CUC_RATE09";
        private string CUC_RATE10 = "@CUC_RATE10";
        private string CUC_TO01 = "@CUC_TO01";
        private string CUC_TO02 = "@CUC_TO02";
        private string CUC_TO03 = "@CUC_TO03";
        private string CUC_TO04 = "@CUC_TO04";
        private string CUC_TO05 = "@CUC_TO05";
        private string CUC_TO06 = "@CUC_TO06";
        private string CUC_TO07 = "@CUC_TO07";
        private string CUC_TO08 = "@CUC_TO08";
        private string CUC_TO09 = "@CUC_TO09";
        private string CUC_TO10 = "@CUC_TO10";
        private string DESCR = "@DESCR";
        private string DIST_MATCHED_MODE = "@DIST_MATCHED_MODE";
        private string DUE_FROM_TO = "@DUE_FROM_TO";
        private string EA_DISCOUNT = "@EA_DISCOUNT";
        private string FIXED_RATE = "@FIXED_RATE";
        private string GENERAL_LEDGER_ID = "@GENERAL_LEDGER_ID";
        private string IP_DISCOUNT = "@IP_DISCOUNT";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LINE_ID = "@LINE_ID";
        private string MAX_CHARGE = "@MAX_CHARGE";
        private string MIN_CHARGE = "@MIN_CHARGE";
        private string PL_DISCOUNT = "@PL_DISCOUNT";
        private string RATE_ID = "@RATE_ID";
        private string RATE_TYPE = "@RATE_TYPE";
        private string SQL_CHECK_RATE_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@RATE_DETAIL WHERE RATE_ID = @RATE_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_RATE_DETAIL = " DELETE FROM @_@RATE_DETAIL WHERE RATE_ID = @RATE_ID ";
        private string SQL_INSERT_RATE_DETAIL = " INSERT INTO @_@RATE_DETAIL ( WH_ID, RATE_ID, LINE_ID, RATE_TYPE, DESCR, CHARGE_MODE, CHARGE_UNIT, CHARGE_PER_UNIT, CHARGE_INVOCATION, TAX_GROUP_ID, GENERAL_LEDGER_ID, MIN_CHARGE, MAX_CHARGE, CARRY_MODE, DIST_MATCHED_MODE, FIXED_RATE, COST_RATE, COSTING_UNIT, COST_PER_UNIT, COST_INVOCATION, EA_DISCOUNT, IP_DISCOUNT, CS_DISCOUNT, PL_DISCOUNT, STC_TO01, STC_FROM01, STC_RATE01, STC_TO02, STC_FROM02, STC_RATE02, STC_TO03, STC_FROM03, STC_RATE03, STC_TO04, STC_FROM04, STC_RATE04, STC_TO05, STC_FROM05, STC_RATE05, STC_TO06, STC_FROM06, STC_RATE06, STC_TO07, STC_FROM07, STC_RATE07, STC_TO08, STC_FROM08, STC_RATE08, STC_TO09, STC_FROM09, STC_RATE09, STC_TO10, STC_FROM10, STC_RATE10, CUC_TO01, CUC_FROM01, CUC_RATE01, CUC_TO02, CUC_FROM02, CUC_RATE02, CUC_TO03, CUC_FROM03, CUC_RATE03, CUC_TO04, CUC_FROM04, CUC_RATE04, CUC_TO05, CUC_FROM05, CUC_RATE05, CUC_TO06, CUC_FROM06, CUC_RATE06, CUC_TO07, CUC_FROM07, CUC_RATE07, CUC_TO08, CUC_FROM08, CUC_RATE08, CUC_TO09, CUC_FROM09, CUC_RATE09, CUC_TO10, CUC_FROM10, CUC_RATE10, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,BILLING_OBJECT,BILLING_ENTITY,DUE_FROM_TO) VALUES ( @WH_ID, @RATE_ID, @LINE_ID, @RATE_TYPE, @DESCR, @CHARGE_MODE, @CHARGE_UNIT, @CHARGE_PER_UNIT, @CHARGE_INVOCATION, @TAX_GROUP_ID, @GENERAL_LEDGER_ID, @MIN_CHARGE, @MAX_CHARGE, @CARRY_MODE, @DIST_MATCHED_MODE, @FIXED_RATE, @COST_RATE, @COSTING_UNIT, @COST_PER_UNIT, @COST_INVOCATION, @EA_DISCOUNT, @IP_DISCOUNT, @CS_DISCOUNT, @PL_DISCOUNT, @STC_TO01, @STC_FROM01, @STC_RATE01, @STC_TO02, @STC_FROM02, @STC_RATE02, @STC_TO03, @STC_FROM03, @STC_RATE03, @STC_TO04, @STC_FROM04, @STC_RATE04, @STC_TO05, @STC_FROM05, @STC_RATE05, @STC_TO06, @STC_FROM06, @STC_RATE06, @STC_TO07, @STC_FROM07, @STC_RATE07, @STC_TO08, @STC_FROM08, @STC_RATE08, @STC_TO09, @STC_FROM09, @STC_RATE09, @STC_TO10, @STC_FROM10, @STC_RATE10, @CUC_TO01, @CUC_FROM01, @CUC_RATE01, @CUC_TO02, @CUC_FROM02, @CUC_RATE02, @CUC_TO03, @CUC_FROM03, @CUC_RATE03, @CUC_TO04, @CUC_FROM04, @CUC_RATE04, @CUC_TO05, @CUC_FROM05, @CUC_RATE05, @CUC_TO06, @CUC_FROM06, @CUC_RATE06, @CUC_TO07, @CUC_FROM07, @CUC_RATE07, @CUC_TO08, @CUC_FROM08, @CUC_RATE08, @CUC_TO09, @CUC_FROM09, @CUC_RATE09, @CUC_TO10, @CUC_FROM10, @CUC_RATE10, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@BILLING_OBJECT,@BILLING_ENTITY,@DUE_FROM_TO )  ";
        private string SQL_SELECT_ALL_RATE_DETAIL = " SELECT  ROWID, WH_ID, RATE_ID, LINE_ID, RATE_TYPE, DESCR, CHARGE_MODE, CHARGE_UNIT, CHARGE_PER_UNIT, CHARGE_INVOCATION, TAX_GROUP_ID, GENERAL_LEDGER_ID, MIN_CHARGE, MAX_CHARGE, CARRY_MODE, DIST_MATCHED_MODE, FIXED_RATE, COST_RATE, COSTING_UNIT, COST_PER_UNIT, COST_INVOCATION, EA_DISCOUNT, IP_DISCOUNT, CS_DISCOUNT, PL_DISCOUNT, STC_TO01, STC_FROM01, STC_RATE01, STC_TO02, STC_FROM02, STC_RATE02, STC_TO03, STC_FROM03, STC_RATE03, STC_TO04, STC_FROM04, STC_RATE04, STC_TO05, STC_FROM05, STC_RATE05, STC_TO06, STC_FROM06, STC_RATE06, STC_TO07, STC_FROM07, STC_RATE07, STC_TO08, STC_FROM08, STC_RATE08, STC_TO09, STC_FROM09, STC_RATE09, STC_TO10, STC_FROM10, STC_RATE10, CUC_TO01, CUC_FROM01, CUC_RATE01, CUC_TO02, CUC_FROM02, CUC_RATE02, CUC_TO03, CUC_FROM03, CUC_RATE03, CUC_TO04, CUC_FROM04, CUC_RATE04, CUC_TO05, CUC_FROM05, CUC_RATE05, CUC_TO06, CUC_FROM06, CUC_RATE06, CUC_TO07, CUC_FROM07, CUC_RATE07, CUC_TO08, CUC_FROM08, CUC_RATE08, CUC_TO09, CUC_FROM09, CUC_RATE09, CUC_TO10, CUC_FROM10, CUC_RATE10, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,BILLING_OBJECT,BILLING_ENTITY,DUE_FROM_TO FROM  @_@RATE_DETAIL ";
        private string SQL_SELECT_ALL_RATEDETAIL_LANGUAGE = "SELECT  RD.ROWID, RD.WH_ID, RD.RATE_ID, RD.LINE_ID, RD.RATE_TYPE,T2.[TEXT] AS RATE_TYPE_D, SCD3.DESCRIPTION AS  DESCR, RD.CHARGE_MODE, T1.[TEXT] AS CHARGE_MODE_D , RD.CHARGE_UNIT,T.[TEXT] AS CHARGE_UNIT_D,  RD.CHARGE_PER_UNIT, RD.CHARGE_INVOCATION, RD.TAX_GROUP_ID, RD.GENERAL_LEDGER_ID, RD.MIN_CHARGE, RD.MAX_CHARGE, RD.CARRY_MODE, RD.DIST_MATCHED_MODE, RD.FIXED_RATE, RD.COST_RATE, RD.COSTING_UNIT, RD.COST_PER_UNIT, RD.COST_INVOCATION, RD.EA_DISCOUNT, RD.IP_DISCOUNT, RD.CS_DISCOUNT, RD.PL_DISCOUNT, RD.STC_TO01, RD.STC_FROM01, RD.STC_RATE01, RD.STC_TO02, RD.STC_FROM02, RD.STC_RATE02, RD.STC_TO03, RD.STC_FROM03, RD.STC_RATE03, RD.STC_TO04, RD.STC_FROM04, RD.STC_RATE04, RD.STC_TO05, RD.STC_FROM05, RD.STC_RATE05, RD.STC_TO06, RD.STC_FROM06, RD.STC_RATE06, RD.STC_TO07, RD.STC_FROM07, RD.STC_RATE07, RD.STC_TO08, RD.STC_FROM08, RD.STC_RATE08, RD.STC_TO09, RD.STC_FROM09, RD.STC_RATE09, RD.STC_TO10, RD.STC_FROM10, RD.STC_RATE10, RD.CUC_TO01, RD.CUC_FROM01, RD.CUC_RATE01, RD.CUC_TO02, RD.CUC_FROM02, RD.CUC_RATE02, RD.CUC_TO03, RD.CUC_FROM03, RD.CUC_RATE03, RD.CUC_TO04, RD.CUC_FROM04, RD.CUC_RATE04, RD.CUC_TO05, RD.CUC_FROM05, RD.CUC_RATE05, RD.CUC_TO06, RD.CUC_FROM06, RD.CUC_RATE06, RD.CUC_TO07, RD.CUC_FROM07, RD.CUC_RATE07, RD.CUC_TO08, RD.CUC_FROM08, RD.CUC_RATE08, RD.CUC_TO09, RD.CUC_FROM09, RD.CUC_RATE09, RD.CUC_TO10, RD.CUC_FROM10, RD.CUC_RATE10, RD.UDF1, RD.UDF2, RD.UDF3, RD.UDF4, RD.UDF5, RD.UDF6, RD.UDF7, RD.UDF8, RD.UDF9, RD.UDF10, RD.IS_ACTIVE, RD.TS, RD.CREATED_BY, RD.CREATED_DATE, RD.UPDATED_BY, RD.UPDATED_DATE,RD.BILLING_OBJECT,RD.BILLING_ENTITY,RD.DUE_FROM_TO FROM  @_@RATE_DETAIL RD  LEFT JOIN  @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.CHARGE_UNIT AND SCD.SYS_CODE_TYPE = 'CHARGEUNIT'  LEFT JOIN SYSADM.TRANSLATION T ON 'CHARGEUNIT.'+RD.CHARGE_UNIT = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY ='@LANGUAGE' LEFT JOIN @_@SYS_CODE_DETAIL SCD1 ON SCD1.SYS_CODE = RD.CHARGE_MODE  AND SCD1.SYS_CODE_TYPE = 'BILLINGMODE' LEFT JOIN SYSADM.TRANSLATION T1 ON 'BILLINGMODE.'+RD.CHARGE_MODE = T1.ITEM_ID  AND  T1.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T1.LANG+'-'+T1.COUNTRY ='@LANGUAGE' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD1.SYS_CODE = RD.RATE_TYPE  AND SCD1.SYS_CODE_TYPE = 'CHARGETYPE' LEFT JOIN SYSADM.TRANSLATION T2 ON 'CHARGETYPE.'+RD.RATE_TYPE = T2.ITEM_ID  AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY ='@LANGUAGE' LEFT JOIN @_@SYS_CODE SC3 ON SC3.SYS_CODE_TYPE = 'COSTTYPE' LEFT JOIN @_@SYS_CODE_DETAIL SCD3 ON SCD3.SYS_CODE_TYPE = SC3.SYS_CODE_TYPE AND SCD3.SYS_CODE = RD.DESCR ";
        private string SQL_UPDATE_RATE_DETAIL = " UPDATE @_@RATE_DETAIL SET WH_ID = @WH_ID, RATE_TYPE = @RATE_TYPE, DESCR = @DESCR, CHARGE_MODE = @CHARGE_MODE, CHARGE_UNIT = @CHARGE_UNIT, CHARGE_PER_UNIT = @CHARGE_PER_UNIT, CHARGE_INVOCATION = @CHARGE_INVOCATION, TAX_GROUP_ID = @TAX_GROUP_ID, GENERAL_LEDGER_ID = @GENERAL_LEDGER_ID, MIN_CHARGE = @MIN_CHARGE, MAX_CHARGE = @MAX_CHARGE, CARRY_MODE = @CARRY_MODE, DIST_MATCHED_MODE = @DIST_MATCHED_MODE, FIXED_RATE = @FIXED_RATE, COST_RATE = @COST_RATE, COSTING_UNIT = @COSTING_UNIT, COST_PER_UNIT = @COST_PER_UNIT, COST_INVOCATION = @COST_INVOCATION, EA_DISCOUNT = @EA_DISCOUNT, IP_DISCOUNT = @IP_DISCOUNT, CS_DISCOUNT = @CS_DISCOUNT, PL_DISCOUNT = @PL_DISCOUNT, STC_TO01 = @STC_TO01, STC_FROM01 = @STC_FROM01, STC_RATE01 = @STC_RATE01, STC_TO02 = @STC_TO02, STC_FROM02 = @STC_FROM02, STC_RATE02 = @STC_RATE02, STC_TO03 = @STC_TO03, STC_FROM03 = @STC_FROM03, STC_RATE03 = @STC_RATE03, STC_TO04 = @STC_TO04, STC_FROM04 = @STC_FROM04, STC_RATE04 = @STC_RATE04, STC_TO05 = @STC_TO05, STC_FROM05 = @STC_FROM05, STC_RATE05 = @STC_RATE05, STC_TO06 = @STC_TO06, STC_FROM06 = @STC_FROM06, STC_RATE06 = @STC_RATE06, STC_TO07 = @STC_TO07, STC_FROM07 = @STC_FROM07, STC_RATE07 = @STC_RATE07, STC_TO08 = @STC_TO08, STC_FROM08 = @STC_FROM08, STC_RATE08 = @STC_RATE08, STC_TO09 = @STC_TO09, STC_FROM09 = @STC_FROM09, STC_RATE09 = @STC_RATE09, STC_TO10 = @STC_TO10, STC_FROM10 = @STC_FROM10, STC_RATE10 = @STC_RATE10, CUC_TO01 = @CUC_TO01, CUC_FROM01 = @CUC_FROM01, CUC_RATE01 = @CUC_RATE01, CUC_TO02 = @CUC_TO02, CUC_FROM02 = @CUC_FROM02, CUC_RATE02 = @CUC_RATE02, CUC_TO03 = @CUC_TO03, CUC_FROM03 = @CUC_FROM03, CUC_RATE03 = @CUC_RATE03, CUC_TO04 = @CUC_TO04, CUC_FROM04 = @CUC_FROM04, CUC_RATE04 = @CUC_RATE04, CUC_TO05 = @CUC_TO05, CUC_FROM05 = @CUC_FROM05, CUC_RATE05 = @CUC_RATE05, CUC_TO06 = @CUC_TO06, CUC_FROM06 = @CUC_FROM06, CUC_RATE06 = @CUC_RATE06, CUC_TO07 = @CUC_TO07, CUC_FROM07 = @CUC_FROM07, CUC_RATE07 = @CUC_RATE07, CUC_TO08 = @CUC_TO08, CUC_FROM08 = @CUC_FROM08, CUC_RATE08 = @CUC_RATE08, CUC_TO09 = @CUC_TO09, CUC_FROM09 = @CUC_FROM09, CUC_RATE09 = @CUC_RATE09, CUC_TO10 = @CUC_TO10, CUC_FROM10 = @CUC_FROM10, CUC_RATE10 = @CUC_RATE10, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),BILLING_OBJECT = @BILLING_OBJECT,BILLING_ENTITY = @BILLING_ENTITY,DUE_FROM_TO = @DUE_FROM_TO WHERE RATE_ID = @RATE_ID AND LINE_ID = @LINE_ID  ";
        private string STC_FROM01 = "@STC_FROM01";
        private string STC_FROM02 = "@STC_FROM02";
        private string STC_FROM03 = "@STC_FROM03";
        private string STC_FROM04 = "@STC_FROM04";
        private string STC_FROM05 = "@STC_FROM05";
        private string STC_FROM06 = "@STC_FROM06";
        private string STC_FROM07 = "@STC_FROM07";
        private string STC_FROM08 = "@STC_FROM08";
        private string STC_FROM09 = "@STC_FROM09";
        private string STC_FROM10 = "@STC_FROM10";
        private string STC_RATE01 = "@STC_RATE01";
        private string STC_RATE02 = "@STC_RATE02";
        private string STC_RATE03 = "@STC_RATE03";
        private string STC_RATE04 = "@STC_RATE04";
        private string STC_RATE05 = "@STC_RATE05";
        private string STC_RATE06 = "@STC_RATE06";
        private string STC_RATE07 = "@STC_RATE07";
        private string STC_RATE08 = "@STC_RATE08";
        private string STC_RATE09 = "@STC_RATE09";
        private string STC_RATE10 = "@STC_RATE10";
        private string STC_TO01 = "@STC_TO01";
        private string STC_TO02 = "@STC_TO02";
        private string STC_TO03 = "@STC_TO03";
        private string STC_TO04 = "@STC_TO04";
        private string STC_TO05 = "@STC_TO05";
        private string STC_TO06 = "@STC_TO06";
        private string STC_TO07 = "@STC_TO07";
        private string STC_TO08 = "@STC_TO08";
        private string STC_TO09 = "@STC_TO09";
        private string STC_TO10 = "@STC_TO10";
        private string TAX_GROUP_ID = "@TAX_GROUP_ID";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckRateDetailIDUnique(string rateID, string LineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RATE_ID", rateID), new SqlParameter("@LINE_ID", LineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_RATE_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckRateDetailIDUnique(DataBase dataBase, DbTransaction tran, string rateID, string LineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RATE_ID", rateID), new SqlParameter("@LINE_ID", LineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_RATE_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteRateDetail(string rateID, List<string> LineID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                string strSql = this.SQL_DELETE_RATE_DETAIL.Replace("@_@", whLoginID) + "AND LINE_ID = @LINE_ID ";
                for (int j = 0; j < LineID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RATE_ID", rateID), new SqlParameter("@LINE_ID", LineID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteRateDetail(DataBase dataBase, DbTransaction tran, string rateID, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = this.SQL_DELETE_RATE_DETAIL.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RATE_ID", rateID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteRateDetail(DataBase dataBase, DbTransaction tran, string rateID, List<string> LineID, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = this.SQL_DELETE_RATE_DETAIL.Replace("@_@", whLoginID) + "AND LINE_ID = @LINE_ID ";
                for (int j = 0; j < LineID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RATE_ID", rateID), new SqlParameter("@LINE_ID", LineID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllRateDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_RATE_DETAIL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllRateDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_RATE_DETAIL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<RateDetailInfo> GetRateDetailByID(string rateID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_RATE_DETAIL.Replace("@_@", whLoginID) + " WHERE RATE_ID = @RATE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RATE_ID", rateID) };
            List<RateDetailInfo> rateDetailList = new List<RateDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    RateDetailInfo rateDetailInfo = new RateDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RATE_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["RATE_TYPE"].ToString(), reader["DESCR"].ToString(), reader["CHARGE_MODE"].ToString(), reader["CHARGE_UNIT"].ToString(), (reader["CHARGE_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["CHARGE_PER_UNIT"].ToString()) : 0M, reader["CHARGE_INVOCATION"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), (reader["MIN_CHARGE"].ToString() != "") ? decimal.Parse(reader["MIN_CHARGE"].ToString()) : 0M, (reader["MAX_CHARGE"].ToString() != "") ? decimal.Parse(reader["MAX_CHARGE"].ToString()) : 0M, reader["CARRY_MODE"].ToString(), reader["DIST_MATCHED_MODE"].ToString(), (reader["FIXED_RATE"].ToString() != "") ? decimal.Parse(reader["FIXED_RATE"].ToString()) : 0M, (reader["COST_RATE"].ToString() != "") ? decimal.Parse(reader["COST_RATE"].ToString()) : 0M, reader["COSTING_UNIT"].ToString(), (reader["COST_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["COST_PER_UNIT"].ToString()) : 0M, reader["COST_INVOCATION"].ToString(), (reader["EA_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["EA_DISCOUNT"].ToString()) : 0M, (reader["IP_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["IP_DISCOUNT"].ToString()) : 0M, (reader["CS_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["CS_DISCOUNT"].ToString()) : 0M, (reader["PL_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["PL_DISCOUNT"].ToString()) : 0M, (reader["STC_TO01"].ToString() != "") ? int.Parse(reader["STC_TO01"].ToString()) : 0, (reader["STC_FROM01"].ToString() != "") ? int.Parse(reader["STC_FROM01"].ToString()) : 0, (reader["STC_RATE01"].ToString() != "") ? decimal.Parse(reader["STC_RATE01"].ToString()) : 0M, (reader["STC_TO02"].ToString() != "") ? int.Parse(reader["STC_TO02"].ToString()) : 0, (reader["STC_FROM02"].ToString() != "") ? int.Parse(reader["STC_FROM02"].ToString()) : 0, (reader["STC_RATE02"].ToString() != "") ? decimal.Parse(reader["STC_RATE02"].ToString()) : 0M, (reader["STC_TO03"].ToString() != "") ? int.Parse(reader["STC_TO03"].ToString()) : 0, (reader["STC_FROM03"].ToString() != "") ? int.Parse(reader["STC_FROM03"].ToString()) : 0, (reader["STC_RATE03"].ToString() != "") ? decimal.Parse(reader["STC_RATE03"].ToString()) : 0M, (reader["STC_TO04"].ToString() != "") ? int.Parse(reader["STC_TO04"].ToString()) : 0, (reader["STC_FROM04"].ToString() != "") ? int.Parse(reader["STC_FROM04"].ToString()) : 0, (reader["STC_RATE04"].ToString() != "") ? decimal.Parse(reader["STC_RATE04"].ToString()) : 0M, (reader["STC_TO05"].ToString() != "") ? int.Parse(reader["STC_TO05"].ToString()) : 0, (reader["STC_FROM05"].ToString() != "") ? int.Parse(reader["STC_FROM05"].ToString()) : 0, (reader["STC_RATE05"].ToString() != "") ? decimal.Parse(reader["STC_RATE05"].ToString()) : 0M, (reader["STC_TO06"].ToString() != "") ? int.Parse(reader["STC_TO06"].ToString()) : 0, (reader["STC_FROM06"].ToString() != "") ? int.Parse(reader["STC_FROM06"].ToString()) : 0, (reader["STC_RATE06"].ToString() != "") ? decimal.Parse(reader["STC_RATE06"].ToString()) : 0M, (reader["STC_TO07"].ToString() != "") ? int.Parse(reader["STC_TO07"].ToString()) : 0, (reader["STC_FROM07"].ToString() != "") ? int.Parse(reader["STC_FROM07"].ToString()) : 0, (reader["STC_RATE07"].ToString() != "") ? decimal.Parse(reader["STC_RATE07"].ToString()) : 0M, (reader["STC_TO08"].ToString() != "") ? int.Parse(reader["STC_TO08"].ToString()) : 0, (reader["STC_FROM08"].ToString() != "") ? int.Parse(reader["STC_FROM08"].ToString()) : 0, (reader["STC_RATE08"].ToString() != "") ? decimal.Parse(reader["STC_RATE08"].ToString()) : 0M, (reader["STC_TO09"].ToString() != "") ? int.Parse(reader["STC_TO09"].ToString()) : 0, (reader["STC_FROM09"].ToString() != "") ? int.Parse(reader["STC_FROM09"].ToString()) : 0, (reader["STC_RATE09"].ToString() != "") ? decimal.Parse(reader["STC_RATE09"].ToString()) : 0M, (reader["STC_TO10"].ToString() != "") ? int.Parse(reader["STC_TO10"].ToString()) : 0, (reader["STC_FROM10"].ToString() != "") ? int.Parse(reader["STC_FROM10"].ToString()) : 0, (reader["STC_RATE10"].ToString() != "") ? decimal.Parse(reader["STC_RATE10"].ToString()) : 0M, (reader["CUC_TO01"].ToString() != "") ? int.Parse(reader["CUC_TO01"].ToString()) : 0, (reader["CUC_FROM01"].ToString() != "") ? int.Parse(reader["CUC_FROM01"].ToString()) : 0, (reader["CUC_RATE01"].ToString() != "") ? decimal.Parse(reader["CUC_RATE01"].ToString()) : 0M, (reader["CUC_TO02"].ToString() != "") ? int.Parse(reader["CUC_TO02"].ToString()) : 0, (reader["CUC_FROM02"].ToString() != "") ? int.Parse(reader["CUC_FROM02"].ToString()) : 0, (reader["CUC_RATE02"].ToString() != "") ? decimal.Parse(reader["CUC_RATE02"].ToString()) : 0M, (reader["CUC_TO03"].ToString() != "") ? int.Parse(reader["CUC_TO03"].ToString()) : 0, (reader["CUC_FROM03"].ToString() != "") ? int.Parse(reader["CUC_FROM03"].ToString()) : 0, (reader["CUC_RATE03"].ToString() != "") ? decimal.Parse(reader["CUC_RATE03"].ToString()) : 0M, (reader["CUC_TO04"].ToString() != "") ? int.Parse(reader["CUC_TO04"].ToString()) : 0, (reader["CUC_FROM04"].ToString() != "") ? int.Parse(reader["CUC_FROM04"].ToString()) : 0, (reader["CUC_RATE04"].ToString() != "") ? decimal.Parse(reader["CUC_RATE04"].ToString()) : 0M, (reader["CUC_TO05"].ToString() != "") ? int.Parse(reader["CUC_TO05"].ToString()) : 0, (reader["CUC_FROM05"].ToString() != "") ? int.Parse(reader["CUC_FROM05"].ToString()) : 0, (reader["CUC_RATE05"].ToString() != "") ? decimal.Parse(reader["CUC_RATE05"].ToString()) : 0M, (reader["CUC_TO06"].ToString() != "") ? int.Parse(reader["CUC_TO06"].ToString()) : 0, (reader["CUC_FROM06"].ToString() != "") ? int.Parse(reader["CUC_FROM06"].ToString()) : 0, (reader["CUC_RATE06"].ToString() != "") ? decimal.Parse(reader["CUC_RATE06"].ToString()) : 0M, (reader["CUC_TO07"].ToString() != "") ? int.Parse(reader["CUC_TO07"].ToString()) : 0, (reader["CUC_FROM07"].ToString() != "") ? int.Parse(reader["CUC_FROM07"].ToString()) : 0, (reader["CUC_RATE07"].ToString() != "") ? decimal.Parse(reader["CUC_RATE07"].ToString()) : 0M, (reader["CUC_TO08"].ToString() != "") ? int.Parse(reader["CUC_TO08"].ToString()) : 0, (reader["CUC_FROM08"].ToString() != "") ? int.Parse(reader["CUC_FROM08"].ToString()) : 0, (reader["CUC_RATE08"].ToString() != "") ? decimal.Parse(reader["CUC_RATE08"].ToString()) : 0M, (reader["CUC_TO09"].ToString() != "") ? int.Parse(reader["CUC_TO09"].ToString()) : 0, (reader["CUC_FROM09"].ToString() != "") ? int.Parse(reader["CUC_FROM09"].ToString()) : 0, (reader["CUC_RATE09"].ToString() != "") ? decimal.Parse(reader["CUC_RATE09"].ToString()) : 0M, (reader["CUC_TO10"].ToString() != "") ? int.Parse(reader["CUC_TO10"].ToString()) : 0, (reader["CUC_FROM10"].ToString() != "") ? int.Parse(reader["CUC_FROM10"].ToString()) : 0, (reader["CUC_RATE10"].ToString() != "") ? decimal.Parse(reader["CUC_RATE10"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), "", "", "", reader["BILLING_OBJECT"].ToString(), reader["BILLING_ENTITY"].ToString(), reader["DUE_FROM_TO"].ToString());
                    rateDetailList.Add(rateDetailInfo);
                }
            }
            return rateDetailList;
        }

        public RateDetailInfo GetRateDetailByID(string rateID, string LineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_RATE_DETAIL.Replace("@_@", whLoginID) + " WHERE RATE_ID = @RATE_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RATE_ID", rateID), new SqlParameter("@LINE_ID", LineID) };
            RateDetailInfo rateDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    rateDetailInfo = new RateDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RATE_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["RATE_TYPE"].ToString(), reader["DESCR"].ToString(), reader["CHARGE_MODE"].ToString(), reader["CHARGE_UNIT"].ToString(), (reader["CHARGE_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["CHARGE_PER_UNIT"].ToString()) : 0M, reader["CHARGE_INVOCATION"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), (reader["MIN_CHARGE"].ToString() != "") ? decimal.Parse(reader["MIN_CHARGE"].ToString()) : 0M, (reader["MAX_CHARGE"].ToString() != "") ? decimal.Parse(reader["MAX_CHARGE"].ToString()) : 0M, reader["CARRY_MODE"].ToString(), reader["DIST_MATCHED_MODE"].ToString(), (reader["FIXED_RATE"].ToString() != "") ? decimal.Parse(reader["FIXED_RATE"].ToString()) : 0M, (reader["COST_RATE"].ToString() != "") ? decimal.Parse(reader["COST_RATE"].ToString()) : 0M, reader["COSTING_UNIT"].ToString(), (reader["COST_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["COST_PER_UNIT"].ToString()) : 0M, reader["COST_INVOCATION"].ToString(), (reader["EA_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["EA_DISCOUNT"].ToString()) : 0M, (reader["IP_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["IP_DISCOUNT"].ToString()) : 0M, (reader["CS_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["CS_DISCOUNT"].ToString()) : 0M, (reader["PL_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["PL_DISCOUNT"].ToString()) : 0M, (reader["STC_TO01"].ToString() != "") ? int.Parse(reader["STC_TO01"].ToString()) : 0, (reader["STC_FROM01"].ToString() != "") ? int.Parse(reader["STC_FROM01"].ToString()) : 0, (reader["STC_RATE01"].ToString() != "") ? decimal.Parse(reader["STC_RATE01"].ToString()) : 0M, (reader["STC_TO02"].ToString() != "") ? int.Parse(reader["STC_TO02"].ToString()) : 0, (reader["STC_FROM02"].ToString() != "") ? int.Parse(reader["STC_FROM02"].ToString()) : 0, (reader["STC_RATE02"].ToString() != "") ? decimal.Parse(reader["STC_RATE02"].ToString()) : 0M, (reader["STC_TO03"].ToString() != "") ? int.Parse(reader["STC_TO03"].ToString()) : 0, (reader["STC_FROM03"].ToString() != "") ? int.Parse(reader["STC_FROM03"].ToString()) : 0, (reader["STC_RATE03"].ToString() != "") ? decimal.Parse(reader["STC_RATE03"].ToString()) : 0M, (reader["STC_TO04"].ToString() != "") ? int.Parse(reader["STC_TO04"].ToString()) : 0, (reader["STC_FROM04"].ToString() != "") ? int.Parse(reader["STC_FROM04"].ToString()) : 0, (reader["STC_RATE04"].ToString() != "") ? decimal.Parse(reader["STC_RATE04"].ToString()) : 0M, (reader["STC_TO05"].ToString() != "") ? int.Parse(reader["STC_TO05"].ToString()) : 0, (reader["STC_FROM05"].ToString() != "") ? int.Parse(reader["STC_FROM05"].ToString()) : 0, (reader["STC_RATE05"].ToString() != "") ? decimal.Parse(reader["STC_RATE05"].ToString()) : 0M, (reader["STC_TO06"].ToString() != "") ? int.Parse(reader["STC_TO06"].ToString()) : 0, (reader["STC_FROM06"].ToString() != "") ? int.Parse(reader["STC_FROM06"].ToString()) : 0, (reader["STC_RATE06"].ToString() != "") ? decimal.Parse(reader["STC_RATE06"].ToString()) : 0M, (reader["STC_TO07"].ToString() != "") ? int.Parse(reader["STC_TO07"].ToString()) : 0, (reader["STC_FROM07"].ToString() != "") ? int.Parse(reader["STC_FROM07"].ToString()) : 0, (reader["STC_RATE07"].ToString() != "") ? decimal.Parse(reader["STC_RATE07"].ToString()) : 0M, (reader["STC_TO08"].ToString() != "") ? int.Parse(reader["STC_TO08"].ToString()) : 0, (reader["STC_FROM08"].ToString() != "") ? int.Parse(reader["STC_FROM08"].ToString()) : 0, (reader["STC_RATE08"].ToString() != "") ? decimal.Parse(reader["STC_RATE08"].ToString()) : 0M, (reader["STC_TO09"].ToString() != "") ? int.Parse(reader["STC_TO09"].ToString()) : 0, (reader["STC_FROM09"].ToString() != "") ? int.Parse(reader["STC_FROM09"].ToString()) : 0, (reader["STC_RATE09"].ToString() != "") ? decimal.Parse(reader["STC_RATE09"].ToString()) : 0M, (reader["STC_TO10"].ToString() != "") ? int.Parse(reader["STC_TO10"].ToString()) : 0, (reader["STC_FROM10"].ToString() != "") ? int.Parse(reader["STC_FROM10"].ToString()) : 0, (reader["STC_RATE10"].ToString() != "") ? decimal.Parse(reader["STC_RATE10"].ToString()) : 0M, (reader["CUC_TO01"].ToString() != "") ? int.Parse(reader["CUC_TO01"].ToString()) : 0, (reader["CUC_FROM01"].ToString() != "") ? int.Parse(reader["CUC_FROM01"].ToString()) : 0, (reader["CUC_RATE01"].ToString() != "") ? decimal.Parse(reader["CUC_RATE01"].ToString()) : 0M, (reader["CUC_TO02"].ToString() != "") ? int.Parse(reader["CUC_TO02"].ToString()) : 0, (reader["CUC_FROM02"].ToString() != "") ? int.Parse(reader["CUC_FROM02"].ToString()) : 0, (reader["CUC_RATE02"].ToString() != "") ? decimal.Parse(reader["CUC_RATE02"].ToString()) : 0M, (reader["CUC_TO03"].ToString() != "") ? int.Parse(reader["CUC_TO03"].ToString()) : 0, (reader["CUC_FROM03"].ToString() != "") ? int.Parse(reader["CUC_FROM03"].ToString()) : 0, (reader["CUC_RATE03"].ToString() != "") ? decimal.Parse(reader["CUC_RATE03"].ToString()) : 0M, (reader["CUC_TO04"].ToString() != "") ? int.Parse(reader["CUC_TO04"].ToString()) : 0, (reader["CUC_FROM04"].ToString() != "") ? int.Parse(reader["CUC_FROM04"].ToString()) : 0, (reader["CUC_RATE04"].ToString() != "") ? decimal.Parse(reader["CUC_RATE04"].ToString()) : 0M, (reader["CUC_TO05"].ToString() != "") ? int.Parse(reader["CUC_TO05"].ToString()) : 0, (reader["CUC_FROM05"].ToString() != "") ? int.Parse(reader["CUC_FROM05"].ToString()) : 0, (reader["CUC_RATE05"].ToString() != "") ? decimal.Parse(reader["CUC_RATE05"].ToString()) : 0M, (reader["CUC_TO06"].ToString() != "") ? int.Parse(reader["CUC_TO06"].ToString()) : 0, (reader["CUC_FROM06"].ToString() != "") ? int.Parse(reader["CUC_FROM06"].ToString()) : 0, (reader["CUC_RATE06"].ToString() != "") ? decimal.Parse(reader["CUC_RATE06"].ToString()) : 0M, (reader["CUC_TO07"].ToString() != "") ? int.Parse(reader["CUC_TO07"].ToString()) : 0, (reader["CUC_FROM07"].ToString() != "") ? int.Parse(reader["CUC_FROM07"].ToString()) : 0, (reader["CUC_RATE07"].ToString() != "") ? decimal.Parse(reader["CUC_RATE07"].ToString()) : 0M, (reader["CUC_TO08"].ToString() != "") ? int.Parse(reader["CUC_TO08"].ToString()) : 0, (reader["CUC_FROM08"].ToString() != "") ? int.Parse(reader["CUC_FROM08"].ToString()) : 0, (reader["CUC_RATE08"].ToString() != "") ? decimal.Parse(reader["CUC_RATE08"].ToString()) : 0M, (reader["CUC_TO09"].ToString() != "") ? int.Parse(reader["CUC_TO09"].ToString()) : 0, (reader["CUC_FROM09"].ToString() != "") ? int.Parse(reader["CUC_FROM09"].ToString()) : 0, (reader["CUC_RATE09"].ToString() != "") ? decimal.Parse(reader["CUC_RATE09"].ToString()) : 0M, (reader["CUC_TO10"].ToString() != "") ? int.Parse(reader["CUC_TO10"].ToString()) : 0, (reader["CUC_FROM10"].ToString() != "") ? int.Parse(reader["CUC_FROM10"].ToString()) : 0, (reader["CUC_RATE10"].ToString() != "") ? decimal.Parse(reader["CUC_RATE10"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), "", "", "", reader["BILLING_OBJECT"].ToString(), reader["BILLING_ENTITY"].ToString(), reader["DUE_FROM_TO"].ToString());
                }
            }
            return rateDetailInfo;
        }

        public List<RateDetailInfo> GetRateDetailByID(string rateID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RATEDETAIL_LANGUAGE.Replace("@_@", whLoginID).Replace("@LANGUAGE", pubParasInfo.UserLanguage) + " WHERE RATE_ID = @RATE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RATE_ID", rateID) };
            List<RateDetailInfo> rateDetailList = new List<RateDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    RateDetailInfo rateDetailInfo = new RateDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RATE_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["RATE_TYPE"].ToString(), reader["DESCR"].ToString(), reader["CHARGE_MODE"].ToString(), reader["CHARGE_UNIT"].ToString(), (reader["CHARGE_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["CHARGE_PER_UNIT"].ToString()) : 0M, reader["CHARGE_INVOCATION"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), (reader["MIN_CHARGE"].ToString() != "") ? decimal.Parse(reader["MIN_CHARGE"].ToString()) : 0M, (reader["MAX_CHARGE"].ToString() != "") ? decimal.Parse(reader["MAX_CHARGE"].ToString()) : 0M, reader["CARRY_MODE"].ToString(), reader["DIST_MATCHED_MODE"].ToString(), (reader["FIXED_RATE"].ToString() != "") ? decimal.Parse(reader["FIXED_RATE"].ToString()) : 0M, (reader["COST_RATE"].ToString() != "") ? decimal.Parse(reader["COST_RATE"].ToString()) : 0M, reader["COSTING_UNIT"].ToString(), (reader["COST_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["COST_PER_UNIT"].ToString()) : 0M, reader["COST_INVOCATION"].ToString(), (reader["EA_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["EA_DISCOUNT"].ToString()) : 0M, (reader["IP_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["IP_DISCOUNT"].ToString()) : 0M, (reader["CS_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["CS_DISCOUNT"].ToString()) : 0M, (reader["PL_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["PL_DISCOUNT"].ToString()) : 0M, (reader["STC_TO01"].ToString() != "") ? int.Parse(reader["STC_TO01"].ToString()) : 0, (reader["STC_FROM01"].ToString() != "") ? int.Parse(reader["STC_FROM01"].ToString()) : 0, (reader["STC_RATE01"].ToString() != "") ? decimal.Parse(reader["STC_RATE01"].ToString()) : 0M, (reader["STC_TO02"].ToString() != "") ? int.Parse(reader["STC_TO02"].ToString()) : 0, (reader["STC_FROM02"].ToString() != "") ? int.Parse(reader["STC_FROM02"].ToString()) : 0, (reader["STC_RATE02"].ToString() != "") ? decimal.Parse(reader["STC_RATE02"].ToString()) : 0M, (reader["STC_TO03"].ToString() != "") ? int.Parse(reader["STC_TO03"].ToString()) : 0, (reader["STC_FROM03"].ToString() != "") ? int.Parse(reader["STC_FROM03"].ToString()) : 0, (reader["STC_RATE03"].ToString() != "") ? decimal.Parse(reader["STC_RATE03"].ToString()) : 0M, (reader["STC_TO04"].ToString() != "") ? int.Parse(reader["STC_TO04"].ToString()) : 0, (reader["STC_FROM04"].ToString() != "") ? int.Parse(reader["STC_FROM04"].ToString()) : 0, (reader["STC_RATE04"].ToString() != "") ? decimal.Parse(reader["STC_RATE04"].ToString()) : 0M, (reader["STC_TO05"].ToString() != "") ? int.Parse(reader["STC_TO05"].ToString()) : 0, (reader["STC_FROM05"].ToString() != "") ? int.Parse(reader["STC_FROM05"].ToString()) : 0, (reader["STC_RATE05"].ToString() != "") ? decimal.Parse(reader["STC_RATE05"].ToString()) : 0M, (reader["STC_TO06"].ToString() != "") ? int.Parse(reader["STC_TO06"].ToString()) : 0, (reader["STC_FROM06"].ToString() != "") ? int.Parse(reader["STC_FROM06"].ToString()) : 0, (reader["STC_RATE06"].ToString() != "") ? decimal.Parse(reader["STC_RATE06"].ToString()) : 0M, (reader["STC_TO07"].ToString() != "") ? int.Parse(reader["STC_TO07"].ToString()) : 0, (reader["STC_FROM07"].ToString() != "") ? int.Parse(reader["STC_FROM07"].ToString()) : 0, (reader["STC_RATE07"].ToString() != "") ? decimal.Parse(reader["STC_RATE07"].ToString()) : 0M, (reader["STC_TO08"].ToString() != "") ? int.Parse(reader["STC_TO08"].ToString()) : 0, (reader["STC_FROM08"].ToString() != "") ? int.Parse(reader["STC_FROM08"].ToString()) : 0, (reader["STC_RATE08"].ToString() != "") ? decimal.Parse(reader["STC_RATE08"].ToString()) : 0M, (reader["STC_TO09"].ToString() != "") ? int.Parse(reader["STC_TO09"].ToString()) : 0, (reader["STC_FROM09"].ToString() != "") ? int.Parse(reader["STC_FROM09"].ToString()) : 0, (reader["STC_RATE09"].ToString() != "") ? decimal.Parse(reader["STC_RATE09"].ToString()) : 0M, (reader["STC_TO10"].ToString() != "") ? int.Parse(reader["STC_TO10"].ToString()) : 0, (reader["STC_FROM10"].ToString() != "") ? int.Parse(reader["STC_FROM10"].ToString()) : 0, (reader["STC_RATE10"].ToString() != "") ? decimal.Parse(reader["STC_RATE10"].ToString()) : 0M, (reader["CUC_TO01"].ToString() != "") ? int.Parse(reader["CUC_TO01"].ToString()) : 0, (reader["CUC_FROM01"].ToString() != "") ? int.Parse(reader["CUC_FROM01"].ToString()) : 0, (reader["CUC_RATE01"].ToString() != "") ? decimal.Parse(reader["CUC_RATE01"].ToString()) : 0M, (reader["CUC_TO02"].ToString() != "") ? int.Parse(reader["CUC_TO02"].ToString()) : 0, (reader["CUC_FROM02"].ToString() != "") ? int.Parse(reader["CUC_FROM02"].ToString()) : 0, (reader["CUC_RATE02"].ToString() != "") ? decimal.Parse(reader["CUC_RATE02"].ToString()) : 0M, (reader["CUC_TO03"].ToString() != "") ? int.Parse(reader["CUC_TO03"].ToString()) : 0, (reader["CUC_FROM03"].ToString() != "") ? int.Parse(reader["CUC_FROM03"].ToString()) : 0, (reader["CUC_RATE03"].ToString() != "") ? decimal.Parse(reader["CUC_RATE03"].ToString()) : 0M, (reader["CUC_TO04"].ToString() != "") ? int.Parse(reader["CUC_TO04"].ToString()) : 0, (reader["CUC_FROM04"].ToString() != "") ? int.Parse(reader["CUC_FROM04"].ToString()) : 0, (reader["CUC_RATE04"].ToString() != "") ? decimal.Parse(reader["CUC_RATE04"].ToString()) : 0M, (reader["CUC_TO05"].ToString() != "") ? int.Parse(reader["CUC_TO05"].ToString()) : 0, (reader["CUC_FROM05"].ToString() != "") ? int.Parse(reader["CUC_FROM05"].ToString()) : 0, (reader["CUC_RATE05"].ToString() != "") ? decimal.Parse(reader["CUC_RATE05"].ToString()) : 0M, (reader["CUC_TO06"].ToString() != "") ? int.Parse(reader["CUC_TO06"].ToString()) : 0, (reader["CUC_FROM06"].ToString() != "") ? int.Parse(reader["CUC_FROM06"].ToString()) : 0, (reader["CUC_RATE06"].ToString() != "") ? decimal.Parse(reader["CUC_RATE06"].ToString()) : 0M, (reader["CUC_TO07"].ToString() != "") ? int.Parse(reader["CUC_TO07"].ToString()) : 0, (reader["CUC_FROM07"].ToString() != "") ? int.Parse(reader["CUC_FROM07"].ToString()) : 0, (reader["CUC_RATE07"].ToString() != "") ? decimal.Parse(reader["CUC_RATE07"].ToString()) : 0M, (reader["CUC_TO08"].ToString() != "") ? int.Parse(reader["CUC_TO08"].ToString()) : 0, (reader["CUC_FROM08"].ToString() != "") ? int.Parse(reader["CUC_FROM08"].ToString()) : 0, (reader["CUC_RATE08"].ToString() != "") ? decimal.Parse(reader["CUC_RATE08"].ToString()) : 0M, (reader["CUC_TO09"].ToString() != "") ? int.Parse(reader["CUC_TO09"].ToString()) : 0, (reader["CUC_FROM09"].ToString() != "") ? int.Parse(reader["CUC_FROM09"].ToString()) : 0, (reader["CUC_RATE09"].ToString() != "") ? decimal.Parse(reader["CUC_RATE09"].ToString()) : 0M, (reader["CUC_TO10"].ToString() != "") ? int.Parse(reader["CUC_TO10"].ToString()) : 0, (reader["CUC_FROM10"].ToString() != "") ? int.Parse(reader["CUC_FROM10"].ToString()) : 0, (reader["CUC_RATE10"].ToString() != "") ? decimal.Parse(reader["CUC_RATE10"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["RATE_TYPE_D"].ToString(), reader["CHARGE_MODE_D"].ToString(), reader["CHARGE_UNIT_D"].ToString(), reader["BILLING_OBJECT"].ToString(), reader["BILLING_ENTITY"].ToString(), reader["DUE_FROM_TO"].ToString());
                    rateDetailList.Add(rateDetailInfo);
                }
            }
            return rateDetailList;
        }

        public RateDetailInfo GetRateDetailByID(DataBase dataBase, DbTransaction tran, string rateID, string LineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_RATE_DETAIL.Replace("@_@", whLoginID) + " WHERE RATE_ID = @RATE_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RATE_ID", rateID), new SqlParameter("@LINE_ID", LineID) };
            RateDetailInfo rateDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                rateDetailInfo = new RateDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RATE_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["RATE_TYPE"].ToString(), reader["DESCR"].ToString(), reader["CHARGE_MODE"].ToString(), reader["CHARGE_UNIT"].ToString(), (reader["CHARGE_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["CHARGE_PER_UNIT"].ToString()) : 0M, reader["CHARGE_INVOCATION"].ToString(), reader["TAX_GROUP_ID"].ToString(), reader["GENERAL_LEDGER_ID"].ToString(), (reader["MIN_CHARGE"].ToString() != "") ? decimal.Parse(reader["MIN_CHARGE"].ToString()) : 0M, (reader["MAX_CHARGE"].ToString() != "") ? decimal.Parse(reader["MAX_CHARGE"].ToString()) : 0M, reader["CARRY_MODE"].ToString(), reader["DIST_MATCHED_MODE"].ToString(), (reader["FIXED_RATE"].ToString() != "") ? decimal.Parse(reader["FIXED_RATE"].ToString()) : 0M, (reader["COST_RATE"].ToString() != "") ? decimal.Parse(reader["COST_RATE"].ToString()) : 0M, reader["COSTING_UNIT"].ToString(), (reader["COST_PER_UNIT"].ToString() != "") ? decimal.Parse(reader["COST_PER_UNIT"].ToString()) : 0M, reader["COST_INVOCATION"].ToString(), (reader["EA_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["EA_DISCOUNT"].ToString()) : 0M, (reader["IP_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["IP_DISCOUNT"].ToString()) : 0M, (reader["CS_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["CS_DISCOUNT"].ToString()) : 0M, (reader["PL_DISCOUNT"].ToString() != "") ? decimal.Parse(reader["PL_DISCOUNT"].ToString()) : 0M, (reader["STC_TO01"].ToString() != "") ? int.Parse(reader["STC_TO01"].ToString()) : 0, (reader["STC_FROM01"].ToString() != "") ? int.Parse(reader["STC_FROM01"].ToString()) : 0, (reader["STC_RATE01"].ToString() != "") ? decimal.Parse(reader["STC_RATE01"].ToString()) : 0M, (reader["STC_TO02"].ToString() != "") ? int.Parse(reader["STC_TO02"].ToString()) : 0, (reader["STC_FROM02"].ToString() != "") ? int.Parse(reader["STC_FROM02"].ToString()) : 0, (reader["STC_RATE02"].ToString() != "") ? decimal.Parse(reader["STC_RATE02"].ToString()) : 0M, (reader["STC_TO03"].ToString() != "") ? int.Parse(reader["STC_TO03"].ToString()) : 0, (reader["STC_FROM03"].ToString() != "") ? int.Parse(reader["STC_FROM03"].ToString()) : 0, (reader["STC_RATE03"].ToString() != "") ? decimal.Parse(reader["STC_RATE03"].ToString()) : 0M, (reader["STC_TO04"].ToString() != "") ? int.Parse(reader["STC_TO04"].ToString()) : 0, (reader["STC_FROM04"].ToString() != "") ? int.Parse(reader["STC_FROM04"].ToString()) : 0, (reader["STC_RATE04"].ToString() != "") ? decimal.Parse(reader["STC_RATE04"].ToString()) : 0M, (reader["STC_TO05"].ToString() != "") ? int.Parse(reader["STC_TO05"].ToString()) : 0, (reader["STC_FROM05"].ToString() != "") ? int.Parse(reader["STC_FROM05"].ToString()) : 0, (reader["STC_RATE05"].ToString() != "") ? decimal.Parse(reader["STC_RATE05"].ToString()) : 0M, (reader["STC_TO06"].ToString() != "") ? int.Parse(reader["STC_TO06"].ToString()) : 0, (reader["STC_FROM06"].ToString() != "") ? int.Parse(reader["STC_FROM06"].ToString()) : 0, (reader["STC_RATE06"].ToString() != "") ? decimal.Parse(reader["STC_RATE06"].ToString()) : 0M, (reader["STC_TO07"].ToString() != "") ? int.Parse(reader["STC_TO07"].ToString()) : 0, (reader["STC_FROM07"].ToString() != "") ? int.Parse(reader["STC_FROM07"].ToString()) : 0, (reader["STC_RATE07"].ToString() != "") ? decimal.Parse(reader["STC_RATE07"].ToString()) : 0M, (reader["STC_TO08"].ToString() != "") ? int.Parse(reader["STC_TO08"].ToString()) : 0, (reader["STC_FROM08"].ToString() != "") ? int.Parse(reader["STC_FROM08"].ToString()) : 0, (reader["STC_RATE08"].ToString() != "") ? decimal.Parse(reader["STC_RATE08"].ToString()) : 0M, (reader["STC_TO09"].ToString() != "") ? int.Parse(reader["STC_TO09"].ToString()) : 0, (reader["STC_FROM09"].ToString() != "") ? int.Parse(reader["STC_FROM09"].ToString()) : 0, (reader["STC_RATE09"].ToString() != "") ? decimal.Parse(reader["STC_RATE09"].ToString()) : 0M, (reader["STC_TO10"].ToString() != "") ? int.Parse(reader["STC_TO10"].ToString()) : 0, (reader["STC_FROM10"].ToString() != "") ? int.Parse(reader["STC_FROM10"].ToString()) : 0, (reader["STC_RATE10"].ToString() != "") ? decimal.Parse(reader["STC_RATE10"].ToString()) : 0M, (reader["CUC_TO01"].ToString() != "") ? int.Parse(reader["CUC_TO01"].ToString()) : 0, (reader["CUC_FROM01"].ToString() != "") ? int.Parse(reader["CUC_FROM01"].ToString()) : 0, (reader["CUC_RATE01"].ToString() != "") ? decimal.Parse(reader["CUC_RATE01"].ToString()) : 0M, (reader["CUC_TO02"].ToString() != "") ? int.Parse(reader["CUC_TO02"].ToString()) : 0, (reader["CUC_FROM02"].ToString() != "") ? int.Parse(reader["CUC_FROM02"].ToString()) : 0, (reader["CUC_RATE02"].ToString() != "") ? decimal.Parse(reader["CUC_RATE02"].ToString()) : 0M, (reader["CUC_TO03"].ToString() != "") ? int.Parse(reader["CUC_TO03"].ToString()) : 0, (reader["CUC_FROM03"].ToString() != "") ? int.Parse(reader["CUC_FROM03"].ToString()) : 0, (reader["CUC_RATE03"].ToString() != "") ? decimal.Parse(reader["CUC_RATE03"].ToString()) : 0M, (reader["CUC_TO04"].ToString() != "") ? int.Parse(reader["CUC_TO04"].ToString()) : 0, (reader["CUC_FROM04"].ToString() != "") ? int.Parse(reader["CUC_FROM04"].ToString()) : 0, (reader["CUC_RATE04"].ToString() != "") ? decimal.Parse(reader["CUC_RATE04"].ToString()) : 0M, (reader["CUC_TO05"].ToString() != "") ? int.Parse(reader["CUC_TO05"].ToString()) : 0, (reader["CUC_FROM05"].ToString() != "") ? int.Parse(reader["CUC_FROM05"].ToString()) : 0, (reader["CUC_RATE05"].ToString() != "") ? decimal.Parse(reader["CUC_RATE05"].ToString()) : 0M, (reader["CUC_TO06"].ToString() != "") ? int.Parse(reader["CUC_TO06"].ToString()) : 0, (reader["CUC_FROM06"].ToString() != "") ? int.Parse(reader["CUC_FROM06"].ToString()) : 0, (reader["CUC_RATE06"].ToString() != "") ? decimal.Parse(reader["CUC_RATE06"].ToString()) : 0M, (reader["CUC_TO07"].ToString() != "") ? int.Parse(reader["CUC_TO07"].ToString()) : 0, (reader["CUC_FROM07"].ToString() != "") ? int.Parse(reader["CUC_FROM07"].ToString()) : 0, (reader["CUC_RATE07"].ToString() != "") ? decimal.Parse(reader["CUC_RATE07"].ToString()) : 0M, (reader["CUC_TO08"].ToString() != "") ? int.Parse(reader["CUC_TO08"].ToString()) : 0, (reader["CUC_FROM08"].ToString() != "") ? int.Parse(reader["CUC_FROM08"].ToString()) : 0, (reader["CUC_RATE08"].ToString() != "") ? decimal.Parse(reader["CUC_RATE08"].ToString()) : 0M, (reader["CUC_TO09"].ToString() != "") ? int.Parse(reader["CUC_TO09"].ToString()) : 0, (reader["CUC_FROM09"].ToString() != "") ? int.Parse(reader["CUC_FROM09"].ToString()) : 0, (reader["CUC_RATE09"].ToString() != "") ? decimal.Parse(reader["CUC_RATE09"].ToString()) : 0M, (reader["CUC_TO10"].ToString() != "") ? int.Parse(reader["CUC_TO10"].ToString()) : 0, (reader["CUC_FROM10"].ToString() != "") ? int.Parse(reader["CUC_FROM10"].ToString()) : 0, (reader["CUC_RATE10"].ToString() != "") ? decimal.Parse(reader["CUC_RATE10"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), "", "", "", reader["BILLING_OBJECT"].ToString(), reader["BILLING_ENTITY"].ToString(), reader["DUE_FROM_TO"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return rateDetailInfo;
        }

        public DataSet GetRateDetailByQueryList(List<string> sqlWhere, RateDetailQueryEntity ratedetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_RATE_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!ratedetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, ratedetailQuery.CurrentPage, ratedetailQuery.PageSize, ratedetailQuery.SortField, ratedetailQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetRateDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, RateDetailQueryEntity ratedetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_RATE_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, ratedetailQuery.CurrentPage, ratedetailQuery.PageSize, ratedetailQuery.SortField, ratedetailQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertRateDetail(RateDetailInfo rateDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                rateDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_RateDetail_Parameters(rateDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_RATE_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertRateDetail(DataBase dataBase, DbTransaction tran, RateDetailInfo rateDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                rateDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_RateDetail_Parameters(rateDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_RATE_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_RateDetail_Parameters(RateDetailInfo rateDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.RATE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.RATE_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.DESCR, SqlDbType.VarChar, 100), new SqlParameter(this.CHARGE_MODE, SqlDbType.VarChar, 20), new SqlParameter(this.CHARGE_UNIT, SqlDbType.VarChar, 20), new SqlParameter(this.CHARGE_PER_UNIT, SqlDbType.Decimal, 13), new SqlParameter(this.CHARGE_INVOCATION, SqlDbType.VarChar, 20), new SqlParameter(this.TAX_GROUP_ID, SqlDbType.VarChar, 20), new SqlParameter(this.GENERAL_LEDGER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.MIN_CHARGE, SqlDbType.Decimal, 13), new SqlParameter(this.MAX_CHARGE, SqlDbType.Decimal, 13), new SqlParameter(this.CARRY_MODE, SqlDbType.VarChar, 20), new SqlParameter(this.DIST_MATCHED_MODE, SqlDbType.VarChar, 20), new SqlParameter(this.FIXED_RATE, SqlDbType.Decimal, 13), 
                new SqlParameter(this.COST_RATE, SqlDbType.Decimal, 13), new SqlParameter(this.COSTING_UNIT, SqlDbType.VarChar, 20), new SqlParameter(this.COST_PER_UNIT, SqlDbType.Decimal, 13), new SqlParameter(this.COST_INVOCATION, SqlDbType.VarChar, 20), new SqlParameter(this.EA_DISCOUNT, SqlDbType.Decimal, 13), new SqlParameter(this.IP_DISCOUNT, SqlDbType.Decimal, 13), new SqlParameter(this.CS_DISCOUNT, SqlDbType.Decimal, 13), new SqlParameter(this.PL_DISCOUNT, SqlDbType.Decimal, 13), new SqlParameter(this.STC_TO01, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM01, SqlDbType.Int, 4), new SqlParameter(this.STC_RATE01, SqlDbType.Decimal, 13), new SqlParameter(this.STC_TO02, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM02, SqlDbType.Int, 4), new SqlParameter(this.STC_RATE02, SqlDbType.Decimal, 13), new SqlParameter(this.STC_TO03, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM03, SqlDbType.Int, 4), 
                new SqlParameter(this.STC_RATE03, SqlDbType.Decimal, 13), new SqlParameter(this.STC_TO04, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM04, SqlDbType.Int, 4), new SqlParameter(this.STC_RATE04, SqlDbType.Decimal, 13), new SqlParameter(this.STC_TO05, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM05, SqlDbType.Int, 4), new SqlParameter(this.STC_RATE05, SqlDbType.Decimal, 13), new SqlParameter(this.STC_TO06, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM06, SqlDbType.Int, 4), new SqlParameter(this.STC_RATE06, SqlDbType.Decimal, 13), new SqlParameter(this.STC_TO07, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM07, SqlDbType.Int, 4), new SqlParameter(this.STC_RATE07, SqlDbType.Decimal, 13), new SqlParameter(this.STC_TO08, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM08, SqlDbType.Int, 4), new SqlParameter(this.STC_RATE08, SqlDbType.Decimal, 13), 
                new SqlParameter(this.STC_TO09, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM09, SqlDbType.Int, 4), new SqlParameter(this.STC_RATE09, SqlDbType.Decimal, 13), new SqlParameter(this.STC_TO10, SqlDbType.Int, 4), new SqlParameter(this.STC_FROM10, SqlDbType.Int, 4), new SqlParameter(this.STC_RATE10, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO01, SqlDbType.Int, 4), new SqlParameter(this.CUC_FROM01, SqlDbType.Int, 4), new SqlParameter(this.CUC_RATE01, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO02, SqlDbType.Int, 4), new SqlParameter(this.CUC_FROM02, SqlDbType.Int, 4), new SqlParameter(this.CUC_RATE02, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO03, SqlDbType.Int, 4), new SqlParameter(this.CUC_FROM03, SqlDbType.Int, 4), new SqlParameter(this.CUC_RATE03, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO04, SqlDbType.Int, 4), 
                new SqlParameter(this.CUC_FROM04, SqlDbType.Int, 4), new SqlParameter(this.CUC_RATE04, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO05, SqlDbType.Int, 4), new SqlParameter(this.CUC_FROM05, SqlDbType.Int, 4), new SqlParameter(this.CUC_RATE05, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO06, SqlDbType.Int, 4), new SqlParameter(this.CUC_FROM06, SqlDbType.Int, 4), new SqlParameter(this.CUC_RATE06, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO07, SqlDbType.Int, 4), new SqlParameter(this.CUC_FROM07, SqlDbType.Int, 4), new SqlParameter(this.CUC_RATE07, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO08, SqlDbType.Int, 4), new SqlParameter(this.CUC_FROM08, SqlDbType.Int, 4), new SqlParameter(this.CUC_RATE08, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO09, SqlDbType.Int, 4), new SqlParameter(this.CUC_FROM09, SqlDbType.Int, 4), 
                new SqlParameter(this.CUC_RATE09, SqlDbType.Decimal, 13), new SqlParameter(this.CUC_TO10, SqlDbType.Int, 4), new SqlParameter(this.CUC_FROM10, SqlDbType.Int, 4), new SqlParameter(this.CUC_RATE10, SqlDbType.Decimal, 13), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), 
                new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.BILLING_OBJECT, SqlDbType.VarChar, 30), new SqlParameter(this.BILLING_ENTITY, SqlDbType.VarChar, 30), new SqlParameter(this.DUE_FROM_TO, SqlDbType.VarChar, 10)
             };
            if (!string.IsNullOrEmpty(rateDetailInfo.WhID))
            {
                paramArray[0].Value = rateDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = rateDetailInfo.RateID;
            paramArray[2].Value = rateDetailInfo.LineID;
            paramArray[3].Value = rateDetailInfo.RateType;
            if (!string.IsNullOrEmpty(rateDetailInfo.Descr))
            {
                paramArray[4].Value = rateDetailInfo.Descr;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.ChargeMode))
            {
                paramArray[5].Value = rateDetailInfo.ChargeMode;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.ChargeUnit))
            {
                paramArray[6].Value = rateDetailInfo.ChargeUnit;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            paramArray[7].Value = rateDetailInfo.ChargePerUnit;
            if (!string.IsNullOrEmpty(rateDetailInfo.ChargeInvocation))
            {
                paramArray[8].Value = rateDetailInfo.ChargeInvocation;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.TaxGroupID))
            {
                paramArray[9].Value = rateDetailInfo.TaxGroupID;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.GeneralLedgerID))
            {
                paramArray[10].Value = rateDetailInfo.GeneralLedgerID;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            paramArray[11].Value = rateDetailInfo.MinCharge;
            paramArray[12].Value = rateDetailInfo.MaxCharge;
            if (!string.IsNullOrEmpty(rateDetailInfo.CarryMode))
            {
                paramArray[13].Value = rateDetailInfo.CarryMode;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.DistMatchedMode))
            {
                paramArray[14].Value = rateDetailInfo.DistMatchedMode;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            paramArray[15].Value = rateDetailInfo.FixedRate;
            paramArray[0x10].Value = rateDetailInfo.CostRate;
            if (!string.IsNullOrEmpty(rateDetailInfo.CostingUnit))
            {
                paramArray[0x11].Value = rateDetailInfo.CostingUnit;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            paramArray[0x12].Value = rateDetailInfo.CostPerUnit;
            if (!string.IsNullOrEmpty(rateDetailInfo.CostInvocation))
            {
                paramArray[0x13].Value = rateDetailInfo.CostInvocation;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            paramArray[20].Value = rateDetailInfo.EaDiscount;
            paramArray[0x15].Value = rateDetailInfo.IpDiscount;
            paramArray[0x16].Value = rateDetailInfo.CsDiscount;
            paramArray[0x17].Value = rateDetailInfo.PlDiscount;
            paramArray[0x18].Value = rateDetailInfo.StcTo01;
            paramArray[0x19].Value = rateDetailInfo.StcFrom01;
            paramArray[0x1a].Value = rateDetailInfo.StcRate01;
            paramArray[0x1b].Value = rateDetailInfo.StcTo02;
            paramArray[0x1c].Value = rateDetailInfo.StcFrom02;
            paramArray[0x1d].Value = rateDetailInfo.StcRate02;
            paramArray[30].Value = rateDetailInfo.StcTo03;
            paramArray[0x1f].Value = rateDetailInfo.StcFrom03;
            paramArray[0x20].Value = rateDetailInfo.StcRate03;
            paramArray[0x21].Value = rateDetailInfo.StcTo04;
            paramArray[0x22].Value = rateDetailInfo.StcFrom04;
            paramArray[0x23].Value = rateDetailInfo.StcRate04;
            paramArray[0x24].Value = rateDetailInfo.StcTo05;
            paramArray[0x25].Value = rateDetailInfo.StcFrom05;
            paramArray[0x26].Value = rateDetailInfo.StcRate05;
            paramArray[0x27].Value = rateDetailInfo.StcTo06;
            paramArray[40].Value = rateDetailInfo.StcFrom06;
            paramArray[0x29].Value = rateDetailInfo.StcRate06;
            paramArray[0x2a].Value = rateDetailInfo.StcTo07;
            paramArray[0x2b].Value = rateDetailInfo.StcFrom07;
            paramArray[0x2c].Value = rateDetailInfo.StcRate07;
            paramArray[0x2d].Value = rateDetailInfo.StcTo08;
            paramArray[0x2e].Value = rateDetailInfo.StcFrom08;
            paramArray[0x2f].Value = rateDetailInfo.StcRate08;
            paramArray[0x30].Value = rateDetailInfo.StcTo09;
            paramArray[0x31].Value = rateDetailInfo.StcFrom09;
            paramArray[50].Value = rateDetailInfo.StcRate09;
            paramArray[0x33].Value = rateDetailInfo.StcTo10;
            paramArray[0x34].Value = rateDetailInfo.StcFrom10;
            paramArray[0x35].Value = rateDetailInfo.StcRate10;
            paramArray[0x36].Value = rateDetailInfo.CucTo01;
            paramArray[0x37].Value = rateDetailInfo.CucFrom01;
            paramArray[0x38].Value = rateDetailInfo.CucRate01;
            paramArray[0x39].Value = rateDetailInfo.CucTo02;
            paramArray[0x3a].Value = rateDetailInfo.CucFrom02;
            paramArray[0x3b].Value = rateDetailInfo.CucRate02;
            paramArray[60].Value = rateDetailInfo.CucTo03;
            paramArray[0x3d].Value = rateDetailInfo.CucFrom03;
            paramArray[0x3e].Value = rateDetailInfo.CucRate03;
            paramArray[0x3f].Value = rateDetailInfo.CucTo04;
            paramArray[0x40].Value = rateDetailInfo.CucFrom04;
            paramArray[0x41].Value = rateDetailInfo.CucRate04;
            paramArray[0x42].Value = rateDetailInfo.CucTo05;
            paramArray[0x43].Value = rateDetailInfo.CucFrom05;
            paramArray[0x44].Value = rateDetailInfo.CucRate05;
            paramArray[0x45].Value = rateDetailInfo.CucTo06;
            paramArray[70].Value = rateDetailInfo.CucFrom06;
            paramArray[0x47].Value = rateDetailInfo.CucRate06;
            paramArray[0x48].Value = rateDetailInfo.CucTo07;
            paramArray[0x49].Value = rateDetailInfo.CucFrom07;
            paramArray[0x4a].Value = rateDetailInfo.CucRate07;
            paramArray[0x4b].Value = rateDetailInfo.CucTo08;
            paramArray[0x4c].Value = rateDetailInfo.CucFrom08;
            paramArray[0x4d].Value = rateDetailInfo.CucRate08;
            paramArray[0x4e].Value = rateDetailInfo.CucTo09;
            paramArray[0x4f].Value = rateDetailInfo.CucFrom09;
            paramArray[80].Value = rateDetailInfo.CucRate09;
            paramArray[0x51].Value = rateDetailInfo.CucTo10;
            paramArray[0x52].Value = rateDetailInfo.CucFrom10;
            paramArray[0x53].Value = rateDetailInfo.CucRate10;
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf1))
            {
                paramArray[0x54].Value = rateDetailInfo.Udf1;
            }
            else
            {
                paramArray[0x54].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf2))
            {
                paramArray[0x55].Value = rateDetailInfo.Udf2;
            }
            else
            {
                paramArray[0x55].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf3))
            {
                paramArray[0x56].Value = rateDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x56].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf4))
            {
                paramArray[0x57].Value = rateDetailInfo.Udf4;
            }
            else
            {
                paramArray[0x57].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf5))
            {
                paramArray[0x58].Value = rateDetailInfo.Udf5;
            }
            else
            {
                paramArray[0x58].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf6))
            {
                paramArray[0x59].Value = rateDetailInfo.Udf6;
            }
            else
            {
                paramArray[0x59].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf7))
            {
                paramArray[90].Value = rateDetailInfo.Udf7;
            }
            else
            {
                paramArray[90].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf8))
            {
                paramArray[0x5b].Value = rateDetailInfo.Udf8;
            }
            else
            {
                paramArray[0x5b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf9))
            {
                paramArray[0x5c].Value = rateDetailInfo.Udf9;
            }
            else
            {
                paramArray[0x5c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.Udf10))
            {
                paramArray[0x5d].Value = rateDetailInfo.Udf10;
            }
            else
            {
                paramArray[0x5d].Value = DBNull.Value;
            }
            paramArray[0x5e].Value = rateDetailInfo.IsActive;
            paramArray[0x5f].Value = rateDetailInfo.CreatedBy;
            paramArray[0x60].Value = rateDetailInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(rateDetailInfo.BillingObject))
            {
                paramArray[0x61].Value = rateDetailInfo.BillingObject;
            }
            else
            {
                paramArray[0x61].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.BillingObject))
            {
                paramArray[0x61].Value = rateDetailInfo.BillingObject;
            }
            else
            {
                paramArray[0x61].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.BillingEntity))
            {
                paramArray[0x62].Value = rateDetailInfo.BillingEntity;
            }
            else
            {
                paramArray[0x62].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(rateDetailInfo.DueFromTo))
            {
                paramArray[0x63].Value = rateDetailInfo.DueFromTo;
                return paramArray;
            }
            paramArray[0x63].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateRateDetail(RateDetailInfo rateDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                rateDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_RateDetail_Parameters(rateDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_RATE_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateRateDetail(DataBase dataBase, DbTransaction tran, RateDetailInfo rateDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                rateDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_RateDetail_Parameters(rateDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_RATE_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

